/**
 * 装备基础数据导入脚本（新格式）
 * 功能：
 * 1) 读取 .env（如存在）与默认配置，连接远程 MySQL 8.0
 * 2) 创建装备基础数据表（如不存在）
 * 3) 读取 ./output/装备基础数据.json，处理新的数据格式
 * 4) 批量导入（存在则更新）
 */

const fs = require('fs');
const path = require('path');
require('dotenv').config();
const mysql = require('mysql2/promise');

// 数据库配置
const dbConf = {
  host: process.env.DB_HOST || '43.143.253.188',
  port: parseInt(process.env.DB_PORT || '3306'),
  user: process.env.DB_USER || 'nextjs',
  password: process.env.DB_PASSWORD || 'yEAiRE2jcxTyRBy5',
  database: process.env.DB_NAME || 'nextjs',
  charset: 'utf8mb4',
  multipleStatements: true
};

const INPUT_FILE = path.join(__dirname, 'output', '装备基础数据.json');

// 装备类型映射
const EQUIPMENT_TYPE_MAP = {
  1: '头盔',
  2: '武器',
  3: '护甲',
  4: '项链',
  5: '护腕',
  6: '鞋子'
};

// 装备基础类型映射
const EQUIPMENT_BASE_MAP = {
  0: '通用',
  1: '武',
  2: '血',
  3: '精',
  4: '速',
  5: '均',
  6: '全'
};

function safeInt (v) {
  if (v === undefined || v === null) return null;
  const n = parseInt(String(v).replace(/[^0-9-]/g, ''), 10);
  return Number.isNaN(n) ? null : n;
}

async function ensureTable (conn) {
  // 先删除旧表（如果存在）
  await conn.execute('DROP TABLE IF EXISTS equipment_base');

  const sql = `
        CREATE TABLE equipment_base (
            id INT NOT NULL PRIMARY KEY COMMENT '装备ID',
            name VARCHAR(255) NULL COMMENT '装备名称',
            type INT NULL COMMENT '装备类型：1-头盔，2-武器，3-护甲，4-项链，5-护腕，6-鞋子',
            type_name VARCHAR(50) NULL COMMENT '装备类型名称',
            base INT NULL COMMENT '装备基础类型：0-通用，1-武，2-血，3-精，4-速，5-均，6-全',
            base_name VARCHAR(50) NULL COMMENT '装备基础类型名称',
            level INT NULL COMMENT '装备等级',
            price INT NULL COMMENT '装备价格',
            raw_json JSON NULL COMMENT '原始数据',
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='装备基础数据表';
    `;
  await conn.execute(sql);
}

async function importData () {
  if (!fs.existsSync(INPUT_FILE)) {
    console.error('未找到数据文件:', INPUT_FILE);
    process.exit(1);
  }

  const rawObj = JSON.parse(fs.readFileSync(INPUT_FILE, 'utf8'));

  const conn = await mysql.createConnection(dbConf);
  try {
    console.log('✅ 已连接 MySQL:', `${dbConf.user}@${dbConf.host}:${dbConf.port}/${dbConf.database}`);
    await ensureTable(conn);

    const upsertSql = `
            INSERT INTO equipment_base
                (id, name, type, type_name, base, base_name, level, price, raw_json)
            VALUES
                (?, ?, ?, ?, ?, ?, ?, ?, CAST(? AS JSON))
            ON DUPLICATE KEY UPDATE
                name=VALUES(name),
                type=VALUES(type),
                type_name=VALUES(type_name),
                base=VALUES(base),
                base_name=VALUES(base_name),
                level=VALUES(level),
                price=VALUES(price),
                raw_json=VALUES(raw_json),
                updated_at=CURRENT_TIMESTAMP
        `;

    let count = 0;
    for (const key of Object.keys(rawObj)) {
      const entry = rawObj[key];
      if (!entry || typeof entry !== 'object') continue;

      const type = entry.type;
      const base = entry.base;
      const data = entry.data;

      if (!type || !Array.isArray(data)) continue;

      const typeName = EQUIPMENT_TYPE_MAP[type] || '未知';
      const baseName = EQUIPMENT_BASE_MAP[base] || '未知';

      // 处理每个装备数据
      for (const item of data) {
        if (!item || typeof item !== 'object') continue;

        const id = safeInt(item.id);
        const name = item.name;
        const level = safeInt(item.lvl);
        const price = safeInt(item.price);

        if (!id || !name) continue;

        const rawJson = JSON.stringify(item);
        await conn.execute(upsertSql, [
          id,
          name,
          type,
          typeName,
          base,
          baseName,
          level,
          price,
          rawJson
        ]);
        count++;
      }
    }

    console.log(`✅ 导入完成，共处理 ${count} 条记录`);

    // 显示统计信息
    const [stats] = await conn.execute(`
            SELECT 
                type_name,
                base_name,
                COUNT(*) as count
            FROM equipment_base 
            GROUP BY type, base, type_name, base_name
            ORDER BY type, base
        `);

    console.log('\n📊 导入统计:');
    console.table(stats);

  } finally {
    await conn.end();
  }
}

if (require.main === module) {
  importData().catch((err) => {
    console.error('导入失败:', err.message);
    process.exit(1);
  });
}

module.exports = { importData };

